<?php

class ClaimsController extends AppController {

    //put your code here
    var $name = "Claims";

    function index() {
        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {

            $this->Property->recursive = -1;
            $this->set("reclamaciones", $this->Claim->find('all', array('conditions' => array('Claim.proyect_id' => $id), 'fields' => array('Claim.id', 'Claim.fecha_recibido', 'Claim.fecha_contestado', 'Claim.observacion', 'Claim.calificacion', 'Claim.radicado_entrada', 'Claim.radicado_salida'))));
            $this->set('cod', $this->Session->read('cod'));
        }
    }

    function delete($id) {

        if ($this->Claim->delete($id, true)) {
            $this->Session->setFlash('Reclamación borrada con éxito');
            $this->redirect(array('controller' => 'Claims', 'action' => 'index'));
        }
    }

    function add() {
        $this->layout = "ajax";
        $id = $this->Session->read('proy_id');
        $this->set('id', $id);
        if (!empty($this->data)) {
            if ($this->Claim->save($this->data)) {
                $last_id = $this->Claim->id;
                App::Import('Model', 'Proyect');
                App::Import('Model', 'ProyectRequirement');
                App::Import('Model', 'ProyectRequirementClaim');
                App::Import('Model', 'CandidateRequirementClaim');
                App::Import('Model', 'PropertyRequirementClaim');
                App::Import('Model', 'TitleStudyClaim');
                $candidate = new CandidateRequirement();

                $proyectRequirements = new ProyectRequirement();
                $proyectRequirements->recursive = -1;
                $dataproy1 = array();

                $pc = new ProyectRequirementClaim();
                $proyectos = $proyectRequirements->find('all', array('conditions' => array('ProyectRequirement.proyect_id' => $id, 'NOT' => array('ProyectRequirement.calificacion' => array(1), 'ProyectRequirement.cal_subsanacion' => array(1))), 'fields' => array('ProyectRequirement.id')));


                foreach ($proyectos as $pr) {
                    if ($pc->find('count', array('conditions' => array('ProyectRequirementClaim.proyect_requirement_id' => $pr['ProyectRequirement']['id'], 'ProyectRequirementClaim.claim_id' => $last_id))) == 0) {
                        $pc->create();
                        $dataproy1 = array('proyect_requirement_id' => $pr['ProyectRequirement']['id'], 'claim_id' => $last_id);
                        if ($pc->save($dataproy1)) {
                            $this->Session->setFlash('OK');
                        } else {
                            $this->Session->setFlash('ERROR');
                        }
                    }
                }

                //ASPIRANTES
                $proyecto = new Proyect();
                $crc = new CandidateRequirementClaim();

                $proyecto->Candidate->CandidateRequirement->recursive = 0;
                $aspirantes = $proyecto->Candidate->CandidateRequirement->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'NOT' => array('CandidateRequirement.calificacion' => array(1), 'CandidateRequirement.cal_subsanacion' => array(1))), 'fields' => array('CandidateRequirement.id')));

                foreach ($aspirantes as $asp) {
                    if ($crc->find('count', array('conditions' => array('CandidateRequirementClaim.candidate_requirement_id' => $asp['CandidateRequirement']['id'], 'CandidateRequirementClaim.claim_id' => $last_id))) == 0) {
                        $crc->create();
                        $datacand = array('candidate_requirement_id' => $asp['CandidateRequirement']['id'], 'claim_id' => $last_id);
                        if ($crc->save($datacand)) {
                            $this->Session->setFlash('OK');
                        } else {
                            $this->Session->setFlash('ERROR');
                        }
                    }
                }

                //PREDIOS

                $prc = new PropertyRequirementClaim();

                $proyecto->Candidate->PropertyRequirement->recursive = 0;
                $predios = $proyecto->Property->PropertyRequirement->find('all', array('conditions' => array('Property.proyect_id' => $id, 'NOT' => array('PropertyRequirement.calificacion' => array(1), 'PropertyRequirement.cal_subsanacion' => array(1))), 'fields' => array('PropertyRequirement.id')));

                foreach ($predios as $asp) {
                    if ($prc->find('count', array('conditions' => array('PropertyRequirementClaim.property_requirement_id' => $asp['PropertyRequirement']['id'], 'PropertyRequirementClaim.claim_id' => $last_id))) == 0) {
                        $prc->create();
                        $datacand = array('property_requirement_id' => $asp['PropertyRequirement']['id'], 'claim_id' => $last_id);
                        if ($prc->save($datacand)) {
                            $this->Session->setFlash('OK');
                        } else {
                            $this->Session->setFlash('ERROR');
                        }
                    }
                }
                $tit = new TitleStudyClaim();

                //ESTUDIOS DE TITULOS
                $proyecto->Property->recursive = -1;
                $predios = $proyecto->Property->find('all', array('conditions' => array('Property.proyect_id' => $id), 'fields' => array('Property.id')));


                foreach ($predios as $asp) {
                    $proyecto->Property->TitleStudy->recursive = -1;
                    $estudio = $proyecto->Property->TitleStudy->find('first', array('conditions' => array('TitleStudy.property_id' => $asp['Property']['id']), 'order' => array('TitleStudy.id DESC'), 'fields' => array('TitleStudy.id', 'TitleStudy.calificacion')));
                    if ($estudio['TitleStudy']['calificacion'] != 1) {
                        if ($tit->find('count', array('conditions' => array('TitleStudyClaim.title_study_id' => $estudio['TitleStudy']['id'], 'TitleStudyClaim.claim_id' => $last_id))) == 0) {
                            $tit->create();
                            $datacand = array('title_study_id' => $estudio['TitleStudy']['id'], 'claim_id' => $last_id);
                            if ($tit->save($datacand)) {
                                $this->Session->setFlash('OK');
                            } else {
                                $this->Session->setFlash('ERROR');
                            }
                        }
                    }
                }
                $this->Session->setFlash('Reclamacion guardada con éxito');
                $this->redirect(array('controller' => 'Claims', 'action' => 'index'));
            }
        }
    }

    function edit($id) {
        $this->layout = "ajax";
        $this->Claim->recursive = -1;
        if (!empty($this->data)) {
            if ($this->Claim->save($this->data)) {
                $this->Session->setFlash('Reclamación editada con éxito');
                $this->redirect(array('controller' => 'Claims', 'action' => 'index'));
            }
        } else {
            $this->data = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id)));
        }
    }

    function total_index($id) {
        $cod = $this->Session->read('cod');
        $this->set('cod', $cod);
        $this->set('id', $id);
        $decision = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id), 'fields' => array('Claim.calificacion', 'Claim.decision', 'Claim.id')));
        $this->set('decision', $decision);
        $requerimientos = $this->Claim->query(
                "SELECT ProyectRequirement.id,ProyectRequirementClaim.id,ProyectRequirementClaim.calificacion,Requirement.texto, ProyectRequirement.cal_subsanacion, ProyectRequirement.calificacion FROM proyect_requirements ProyectRequirement 
                           LEFT JOIN requirements Requirement ON ProyectRequirement.requirement_id = Requirement.id 
                           LEFT JOIN proyect_requirement_claims ProyectRequirementClaim ON ProyectRequirementClaim.proyect_requirement_id=ProyectRequirement.id  
                           WHERE ProyectRequirementClaim.claim_id=$id");
        $this->set('requisitos', $requerimientos);

        //ASPIRANTES

        $requerimientos = $this->Claim->query(
                "SELECT Candidate.nro_ident,CandidateRequirementClaim.id, CandidateRequirementClaim.calificacion, Candidate.1er_nombre, Candidate.1er_apellido, CandidateRequirement.id , Requirement.texto, CandidateRequirement.cal_subsanacion, CandidateRequirement.calificacion
                           FROM candidate_requirements CandidateRequirement
                           LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                           LEFT JOIN candidates Candidate ON CandidateRequirement.candidate_id = Candidate.id
                           LEFT JOIN candidate_requirement_claims CandidateRequirementClaim ON CandidateRequirementClaim.candidate_requirement_id=CandidateRequirement.id
                           WHERE CandidateRequirementClaim.claim_id =$id");
        $this->set('requisitos1', $requerimientos);
        //PREDIOS            
        $requerimientos = $this->Claim->query(
                "SELECT PropertyRequirementClaim.id,PropertyRequirementClaim.calificacion, Property.matricula,Property.codigo,PropertyRequirement.id ,Requirement.texto, PropertyRequirement.cal_subsanacion, PropertyRequirement.calificacion FROM property_requirements PropertyRequirement 
                           LEFT JOIN requirements Requirement ON PropertyRequirement.requirement_id = Requirement.id 
                           LEFT JOIN properties Property on PropertyRequirement.property_id=Property.id  
                           LEFT JOIN property_requirement_claims PropertyRequirementClaim ON PropertyRequirementClaim.property_requirement_id=PropertyRequirement.id
                           WHERE PropertyRequirementClaim.claim_id =$id"
        );
        $this->set('requisitos2', $requerimientos);
        //TITULOS
        $titulos = $this->Claim->query(
                "SELECT TitleStudyClaim.id, TitleStudyClaim.calificacion, TitleStudyClaim.concepto, Property.codigo,TitleStudy.calificacion,TitleStudy.concepto FROM title_study_claims TitleStudyClaim 
                           LEFT JOIN title_studies TitleStudy ON TitleStudy.id =TitleStudyClaim.title_study_id
                           LEFT JOIN properties Property on TitleStudy.property_id=Property.id  
                           WHERE TitleStudyClaim.claim_id =$id"
        );

        $this->set('titulos', $titulos);
    }

    function recalification_index($id) {

        $cod = $this->Session->read('cod');
        $this->set('cod', $cod);
        $this->set('id', $id);
        $decision = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id), 'fields' => array('Claim.calificacion_territorial', 'Claim.concepto_territorial', 'Claim.id', 'Claim.calificacion')));

        if ($decision['Claim']['calificacion'] != "Evaluar nuevamente") {
            $this->Session->setFlash("Este proyecto no se debe evaluar nuevamente");
            $this->redirect(array('Controler' => 'claims', 'action' => 'index'));
            exit();
        }

        $requerimientos = $this->Claim->query(
                "SELECT ProyectRequirement.id,ProyectRequirementClaim.id,ProyectRequirementClaim.calificacion,Requirement.texto, ProyectRequirement.cal_subsanacion, ProyectRequirement.calificacion FROM proyect_requirements ProyectRequirement 
                           LEFT JOIN requirements Requirement ON ProyectRequirement.requirement_id = Requirement.id 
                           LEFT JOIN proyect_requirement_claims ProyectRequirementClaim ON ProyectRequirementClaim.proyect_requirement_id=ProyectRequirement.id  
                           WHERE ProyectRequirementClaim.claim_id=$id");
        $this->set('requisitos', $requerimientos);

        //ASPIRANTES

        $requerimientos = $this->Claim->query(
                "SELECT Distinct Candidate.id,Candidate.1er_nombre,Candidate.1er_apellido,Candidate.nro_ident
                           FROM candidate_requirements CandidateRequirement
                           LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                           LEFT JOIN candidates Candidate ON CandidateRequirement.candidate_id = Candidate.id
                           LEFT JOIN candidate_requirement_claims CandidateRequirementClaim ON CandidateRequirementClaim.candidate_requirement_id=CandidateRequirement.id
                           WHERE CandidateRequirementClaim.claim_id =$id");
        $this->set('requisitos1', $requerimientos);
        //PREDIOS            
        $requerimientos = $this->Claim->query(
                "SELECT Distinct Property.id,Property.codigo,Property.nombre   FROM property_requirements PropertyRequirement 
                           LEFT JOIN requirements Requirement ON PropertyRequirement.requirement_id = Requirement.id 
                           LEFT JOIN properties Property on PropertyRequirement.property_id=Property.id  
                           LEFT JOIN property_requirement_claims PropertyRequirementClaim ON PropertyRequirementClaim.property_requirement_id=PropertyRequirement.id
                           WHERE PropertyRequirementClaim.claim_id =$id"
        );
        $this->set('requisitos2', $requerimientos);
        //TITULOS
        $titulos = $this->Claim->query(
                "SELECT Distinct Property.id,Property.codigo,Property.nombre  FROM title_study_claims TitleStudyClaim 
                           LEFT JOIN title_studies TitleStudy ON TitleStudy.id =TitleStudyClaim.title_study_id
                           LEFT JOIN properties Property on TitleStudy.property_id=Property.id  
                           WHERE TitleStudyClaim.claim_id =$id"
        );

        $this->set('titulos', $titulos);
        $this->set('decision', $this->Claim->find('first', array('conditions' => array('Claim.id' => $id))));
    }

    function calification($id) {
        if (empty($this->data)) {
            $this->data = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id), 'fields' => array('Claim.calificacion', 'Claim.decision', 'Claim.id')));
        } else {
            if ($this->Claim->save($this->data)) {
                $this->Session->setFlash('Registro editado con éxito');
                $this->redirect(array('controller' => 'Claims', 'action' => 'total_index', $this->data['Claim']['id']));
            }
        }
    }

    function territorial_calification($id) {
        if (empty($this->data)) {
            $this->data = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id), 'fields' => array('Claim.reviso_territorial', 'Claim.preparo_territorial', 'Claim.calificacion_territorial', 'Claim.concepto_territorial', 'Claim.id')));
        } else {
            if ($this->Claim->save($this->data)) {
                $this->Session->setFlash('Registro editado con éxito');
                $this->redirect(array('controller' => 'Claims', 'action' => 'recalification_index', $this->data['Claim']['id']));
            }
        }
    }

    function print_letter($idReclamacion) {
        $this->layout = "pdf";
        $id = $this->Session->read('proy_id');
        $codigo = $this->Session->read('cod');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $this->set('proyect_id', $id);
            $this->set('codigo', $codigo);

            //Obtengo los datos de la reclamación que se van a imprimir.     

            $this->set('reclamacion', $this->Claim->find('first', array('conditions' => array('Claim.id' => $idReclamacion), 'fields' => array('Claim.fecha_recibido', 'Claim.observacion', 'Claim.calificacion', 'Claim.decision'))));




            $requerimientos = $this->Claim->query(
                    "SELECT ProyectRequirementClaim.concepto, ProyectRequirement.id,ProyectRequirementClaim.id,ProyectRequirementClaim.calificacion ,Requirement.texto, ProyectRequirement.cal_subsanacion, ProyectRequirement.calificacion FROM proyect_requirements ProyectRequirement 
                           LEFT JOIN requirements Requirement ON ProyectRequirement.requirement_id = Requirement.id 
                           LEFT JOIN proyect_requirement_claims ProyectRequirementClaim ON ProyectRequirementClaim.proyect_requirement_id=ProyectRequirement.id  
                           WHERE ProyectRequirementClaim.claim_id=$idReclamacion");
            $this->set('requisitos', $requerimientos);

            //ASPIRANTES

            $requerimientos = $this->Claim->query(
                    "SELECT CandidateRequirementClaim.concepto, Candidate.nro_ident,CandidateRequirementClaim.id,CandidateRequirementClaim.calificacion, Candidate.1er_nombre, Candidate.1er_apellido, CandidateRequirement.id , Requirement.texto, CandidateRequirement.cal_subsanacion, CandidateRequirement.calificacion
                           FROM candidate_requirements CandidateRequirement
                           LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                           LEFT JOIN candidates Candidate ON CandidateRequirement.candidate_id = Candidate.id
                           LEFT JOIN candidate_requirement_claims CandidateRequirementClaim ON CandidateRequirementClaim.candidate_requirement_id=CandidateRequirement.id
                           WHERE CandidateRequirementClaim.claim_id =$idReclamacion");
            $this->set('requisitos1', $requerimientos);
            //PREDIOS            
            $requerimientos = $this->Claim->query(
                    "SELECT PropertyRequirementClaim.concepto,PropertyRequirementClaim.id,PropertyRequirementClaim.calificacion,Property.matricula,Property.codigo,PropertyRequirement.id ,Requirement.texto, PropertyRequirement.cal_subsanacion, PropertyRequirement.calificacion FROM property_requirements PropertyRequirement 
                           LEFT JOIN requirements Requirement ON PropertyRequirement.requirement_id = Requirement.id 
                           LEFT JOIN properties Property on PropertyRequirement.property_id=Property.id  
                           LEFT JOIN property_requirement_claims PropertyRequirementClaim ON PropertyRequirementClaim.property_requirement_id=PropertyRequirement.id
                           WHERE PropertyRequirementClaim.claim_id =$idReclamacion"
            );
            $this->set('requisitos2', $requerimientos);

            //$decision = $this->Claim->find('first', array('conditions' => array('Claim.id' => $id), 'fields' => array('Claim.calificacion', 'Claim.decision', 'Claim.id')));
            //$this->set('decision', $decision);
            //TITULOS
            $titulos = $this->Claim->query(
                    "SELECT TitleStudyClaim.concepto,TitleStudyClaim.id,TitleStudyClaim.calificacion,TitleStudyClaim.concepto,Property.codigo,TitleStudy.calificacion,TitleStudy.concepto  FROM title_study_claims TitleStudyClaim 
                           LEFT JOIN title_studies TitleStudy ON TitleStudy.id =TitleStudyClaim.title_study_id
                           LEFT JOIN properties Property on TitleStudy.property_id=Property.id  
                           WHERE TitleStudyClaim.claim_id =$idReclamacion"
            );

            $this->set('titulos', $titulos);
        }
    }

    function carta_acepta_reclamacion($idReclamacion) {
        $this->layout = "pdf";
        $id = $this->Session->read('proy_id');
        $codigo = $this->Session->read('cod');
        if ($id == "") {
            $this->Session->setFlash('No ha seleccionado Proyecto');
            $this->redirect(array('controller' => 'Proyects', 'action' => 'index'));
        } else {
            $proyecto = $this->Claim->Proyect->recursive = 0;
            $proyecto = $this->Claim->Proyect->find('first', array('conditions' => array('Proyect.id' => $id), 'fields' => array('Branch.nombre')));
            $this->set('proyecto', $proyecto);
            $this->set('proyect_id', $id);
            $this->set('codigo', $codigo);

            //Obtengo los datos de la reclamación que se van a imprimir.
            $this->Claim->recursive = -1;
            $reclamacion = $this->Claim->find('first', array('conditions' => array('Claim.id' => $idReclamacion), 'fields' => array('Claim.decision', 'Claim.fecha_sesion', 'Claim.acta', 'Claim.calificacion', 'Claim.radicado_entrada', 'Claim.fecha_recibido')));
            $this->set('reclamacion', $reclamacion);


            $proponente = $this->Claim->query(
                    "SELECT Proposer.nombre, Proposer.direccion, Proposer.telefono_fijo, Proposer.telefono_celular, Departament.name, City.name 
                FROM proposers Proposer
                LEFT JOIN proyects Proyect ON Proposer.proyect_id = Proyect.id
                LEFT JOIN cities City ON Proposer.city_id = City.id
                LEFT JOIN departaments Departament ON City.departament_id = Departament.id
                WHERE Proposer.proyect_id = $id"
            );
            $this->set('proponente', $proponente);

            if ($reclamacion['Claim']['calificacion'] == 'Ratifica rechazo') {
                //Obtengo los datos de la reclamación que se van a imprimir.     


                $requerimientos = $this->Claim->query(
                        "SELECT ProyectRequirementClaim.concepto, ProyectRequirement.id,ProyectRequirementClaim.id,ProyectRequirementClaim.calificacion ,Requirement.texto, ProyectRequirement.cal_subsanacion, ProyectRequirement.calificacion FROM proyect_requirements ProyectRequirement 
                           LEFT JOIN requirements Requirement ON ProyectRequirement.requirement_id = Requirement.id 
                           LEFT JOIN proyect_requirement_claims ProyectRequirementClaim ON ProyectRequirementClaim.proyect_requirement_id=ProyectRequirement.id  
                           WHERE ProyectRequirementClaim.claim_id=$idReclamacion");
                $this->set('requisitos', $requerimientos);

                //ASPIRANTES

                $requerimientos = $this->Claim->query(
                        "SELECT CandidateRequirementClaim.concepto, Candidate.nro_ident,CandidateRequirementClaim.id,CandidateRequirementClaim.calificacion, Candidate.1er_nombre, Candidate.1er_apellido, CandidateRequirement.id , Requirement.texto, CandidateRequirement.cal_subsanacion, CandidateRequirement.calificacion
                           FROM candidate_requirements CandidateRequirement
                           LEFT JOIN requirements Requirement ON CandidateRequirement.requirement_id = Requirement.id
                           LEFT JOIN candidates Candidate ON CandidateRequirement.candidate_id = Candidate.id
                           LEFT JOIN candidate_requirement_claims CandidateRequirementClaim ON CandidateRequirementClaim.candidate_requirement_id=CandidateRequirement.id
                           WHERE CandidateRequirementClaim.claim_id =$idReclamacion");
                $this->set('requisitos1', $requerimientos);
                //PREDIOS            
                $requerimientos = $this->Claim->query(
                        "SELECT PropertyRequirementClaim.concepto,PropertyRequirementClaim.id,PropertyRequirementClaim.calificacion,Property.matricula,Property.codigo,PropertyRequirement.id ,Requirement.texto, PropertyRequirement.cal_subsanacion, PropertyRequirement.calificacion FROM property_requirements PropertyRequirement 
                           LEFT JOIN requirements Requirement ON PropertyRequirement.requirement_id = Requirement.id 
                           LEFT JOIN properties Property on PropertyRequirement.property_id=Property.id  
                           LEFT JOIN property_requirement_claims PropertyRequirementClaim ON PropertyRequirementClaim.property_requirement_id=PropertyRequirement.id
                           WHERE PropertyRequirementClaim.claim_id =$idReclamacion"
                );
                $this->set('requisitos2', $requerimientos);

                //TITULOS
                $titulos = $this->Claim->query(
                        "SELECT TitleStudyClaim.concepto,TitleStudyClaim.id,TitleStudyClaim.calificacion,TitleStudyClaim.concepto,Property.codigo,TitleStudy.calificacion,TitleStudy.concepto  FROM title_study_claims TitleStudyClaim 
                           LEFT JOIN title_studies TitleStudy ON TitleStudy.id =TitleStudyClaim.title_study_id
                           LEFT JOIN properties Property on TitleStudy.property_id=Property.id  
                           WHERE TitleStudyClaim.claim_id =$idReclamacion"
                );

                $this->set('titulos', $titulos);
            }
        }
    }

    function report() {

        $this->layout = 'csv';
        ini_set('max_execution_time', 600); //increase max_execution_time to 10 min if data set is very large
        //create a file
        date_default_timezone_set("America/Bogota");
        $filename = "Reclamaciones_" . date("Y-m-d") . ".csv";
        $csv_file = fopen('php://output', 'w');
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $sql = "SELECT b.codigo, a.fecha_recibido, a.fecha_contestado, a.observacion, a.calificacion, a.radicado_entrada, a.radicado_salida, a.decision, a.fecha_sesion, a.acta
                FROM claims a
                LEFT JOIN proyects b ON a.proyect_id = b.id
                WHERE 1
                ORDER BY b.codigo ASC";

        $results = $this->Claim->query($sql);

        // The column headings of your .csv file
        $header_row = array("codigo", "fecha recibido", "fecha contestado", "observación", "calificación", "radicado entrada", "radicado salida", "decisión", "fecha sesión", "acta");
        fputcsv($csv_file, $header_row, ';', '"');

        // Each iteration of this while loop will be a row in your .csv file where each field corresponds to the heading of the column
        foreach ($results as $result) {
            // Array indexes correspond to the field names in your db table(s)
            $row = array(
                $result['b']['codigo'],
                $result['a']['fecha_recibido'],
                $result['a']['fecha_contestado'],
                $result['a']['observacion'],
                $result['a']['calificacion'],
                $result['a']['radicado_entrada'],
                $result['a']['radicado_salida'],
                $result['a']['decision'],
                $result['a']['fecha_sesion'],
                $result['a']['acta']
            );
            fputcsv($csv_file, $row, ';', '"');
        }
        fclose($csv_file);
    }

    function report_new_evaluation() {

        $this->layout = 'csv';
        ini_set('max_execution_time', 600); //increase max_execution_time to 10 min if data set is very large
        //create a file
        date_default_timezone_set("America/Bogota");
        $filename = "Reclamaciones_" . date("Y-m-d") . ".csv";
        $csv_file = fopen('php://output', 'w');
        header('Content-type: application/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $sql = "SELECT b.codigo, a.calificacion, a.calificacion_territorial, a.concepto_territorial FROM `claims` a 
                LEFT JOIN proyects b ON a.proyect_id = b.id
                WHERE a.calificacion = 'EVALUAR NUEVAMENTE'";

        $results = $this->Claim->query($sql);

        // The column headings of your .csv file
        $header_row = array("CÓDIGO", "CALIFICACIÓN COMITÉ", "CALIFICACIÓN TERRITORIAL", "CONCEPTO TERRITORIAL");
        fputcsv($csv_file, $header_row, ';', '"');

        // Each iteration of this while loop will be a row in your .csv file where each field corresponds to the heading of the column
        foreach ($results as $result) {
            // Array indexes correspond to the field names in your db table(s)
            $calificacion_territorial = "";
            $concepto_territorial = "";
            if (!is_null($result['a']['calificacion_territorial'])) {
                $calificacion_territorial = $result['a']['calificacion_territorial'];
            }
            if (!is_null($result['a']['concepto_territorial'])) {
                $concepto_territorial = $result['a']['concepto_territorial'];
            }

            $row = array(
                $result['b']['codigo'],
                $result['a']['calificacion'],
                $calificacion_territorial,
                $concepto_territorial
            );
            fputcsv($csv_file, $row, ';', '"');
        }
        fclose($csv_file);
    }

    function claim_qualification() {
        $this->layout = "ajax";



        App::Import('model', 'Candidate');
        //calificando Aspirantes
        $this->Claim->recursive = -1;
        $id = $this->Session->read('proy_id');
        $claim = $this->Claim->find('first', array('conditions' => array('Claim.proyect_id' => $id), 'fields' => array('Claim.id', 'Claim.proyect_id', 'Claim.calificacion')));
        $claim_id = $claim['Claim']['id'];
        $treclamaciones = $this->Claim->find('all', array('conditions' => array('Claim.calificacion' => array('Ratifica rechazo', 'Acepta reclamacion')), 'fields' => array('Claim.id', 'Claim.proyect_id')));

        // foreach ($treclamaciones as $treclamacion) {

        $tabla = "<table border=1><tbody><tr><td></td></tr>";

        //$id = $treclamacion['Claim']['proyect_id'];
        //$claim_id=$treclamacion['Claim']['id']; 
        $asp = new Candidate();
        $asp->recursive = 0;
        $aspirantes = $asp->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id' => 0), 'fields' => array('Candidate.id', 'Candidate.candidate_id', 'Candidate.nro_ident', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.1er_nombre')));


        if ($claim['Claim']['calificacion'] == "Ratifica rechazo" || $claim['Claim']['calificacion'] == "Acepta reclamacion") {

            foreach ($aspirantes as $aspirante) {


                $cumple = 1;
                $fl = 0;
                $nombre = $aspirante['Candidate']['1er_nombre'] . "" . $aspirante['Candidate']['1er_apellido'];
                $requisitos = $asp->CandidateRequirement->CandidateRequirementClaim->find('all', array('conditions' => array('CandidateRequirement.candidate_id' => $aspirante['Candidate']['id'], 'CandidateRequirementClaim.claim_id' => $claim_id), 'fields' => array('CandidateRequirementClaim.calificacion', 'CandidateRequirementClaim.id')));
                foreach ($requisitos as $requisito) {
                    if (is_null($requisito['CandidateRequirementClaim']['calificacion']) || $requisito['CandidateRequirementClaim']['calificacion'] == 'Sin registro' || $requisito['CandidateRequirementClaim']['calificacion'] == 'Ratifica la causal') {
                        $cumple = 0;
                    }
                    $fl = 1;
                }

                if ($cumple == 0) {

                    $asp->id = $aspirante['Candidate']['id'];
                    $asp->saveField('estado_filtro', 4);
                    $asp->updateAll(array("Candidate.estado_filtro" => 4), array('Candidate.candidate_id' => $aspirante['Candidate']['id']));
                    $tabla.="<tr><td>EL aspirante $nombre no cumple con los requisitos se rechaza asi como su nucleo familiar</td></tr>";
                } elseif ($fl == 1 AND $cumple == 1) {
                    $asp->id = $aspirante['Candidate']['id'];
                    $asp->saveField('estado_filtro', 5);
                }

                //CALIFICACION DE LOS CONYUGES
                $conyuges = $asp->find('all', array('conditions' => array('Candidate.proyect_id' => $id, 'Candidate.candidate_id !=' => 0), 'fields' => array('Candidate.id', 'Candidate.candidate_id', 'Candidate.nro_ident', 'Candidate.1er_apellido', 'Candidate.2do_apellido', 'Candidate.1er_nombre')));
                foreach ($conyuges as $conyuge) {
                    $cumple1 = 1;


                    $nombreCon = $conyuge['Candidate']['1er_nombre'] . "" . $conyuge['Candidate']['1er_apellido'] . "<br>";

                    $requisitosCon = $asp->CandidateRequirement->CandidateRequirementClaim->find('all', array('conditions' => array('CandidateRequirement.candidate_id' => $conyuge['Candidate']['id'], 'CandidateRequirementClaim.claim_id' => $claim_id), 'fields' => array('CandidateRequirementClaim.calificacion', 'CandidateRequirementClaim.id')));

                    foreach ($requisitosCon as $requisitoCon) {
                        if (is_null($requisitoCon['CandidateRequirementClaim']['calificacion']) || $requisitoCon['CandidateRequirementClaim']['calificacion'] == 'Sin registro' || $requisitoCon['CandidateRequirementClaim']['calificacion'] == 'Ratifica la causal') {
                            $cumple1 = 0;
                        }
                    }

                    if ($cumple1 == 0) {
                        $asp->updateAll(array("Candidate.estado_filtro" => 4), array('Candidate.id' => $conyuge['Candidate']['id']));
                        $asp->updateAll(array("Candidate.estado_filtro" => 4), array('Candidate.id' => $conyuge['Candidate']['candidate_id']));
                        $tabla.="<tr><td>EL aspirante $nombreCon no cumple con los requisitos se rechaza asi como su nucleo familiar</td></tr>";
                    } elseif ($cumple1 == 1) {
                        $estado = 1;
                        $asp->updateAll(array("Candidate.estado_filtro" => 5), array('Candidate.id' => $conyuge['Candidate']['id']));
                        $estado = $asp->field('estado_filtro', array('Candidate.id' => $conyuge['Candidate']['candidate_id']));
                        if ($estado == 0) {
                            $conteo = $asp->CandidateRequirement->find('count', array('conditions' => array('CandidateRequirement.candidate_id' => $conyuge['Candidate']['candidate_id'], 'CandidateRequirement.calificacion !=' => 2)));
                            if ($conteo == 0) {
                                $asp->updateAll(array("Candidate.estado_filtro" => 5), array('Candidate.id' => $conyuge['Candidate']['candidate_id']));
                            }
                        }
                    }
                }
            }
            $tabla.="</tbody></table>";

            echo $tabla;
        } elseif ($claim['Claim']['calificacion'] == "Evaluar nuevamente") {


            foreach ($aspirantes as $aspirante) {


                $cumple = 1;
                $fl = 0;
                $nombre = $aspirante['Candidate']['1er_nombre'] . "" . $aspirante['Candidate']['1er_apellido'];
                $requisitos = $asp->CandidateRequirement->find('all', array('conditions' => array('CandidateRequirement.candidate_id' => $aspirante['Candidate']['id'], 'CandidateRequirement.claim_id' => $claim_id), 'fields' => array('CandidateRequirement.calificacion', 'CandidateRequirement.cal_subsanacion')));
                foreach ($requisitos as $requisito) {
                    if (is_null($requisito['CandidateRequirement']['calificacion']) || $requisito['CandidateRequirement']['calificacion'] == 0) {
                        $cumple = 0;
                        break;
                    } elseif ($requisito['CandidateRequirement']['calificacion'] == 2) {
                        $cumple = 0;
                        break;
                    } elseif ($requisito['CandidateRequirement']['calificacion'] == 3) {
                        if ($requisito['CandidateRequirement']['calificacion'] != 1 || is_null($requisito['CandidateRequirement']['calificacion'])) {
                            $cumple = 0;
                            break;
                        }
                    }
                }

                if (!empty($requisitos) and $cumple == 1) {
                    $asp->id = $aspirante['Candidate']['id'];
                    $asp->saveField('estado_filtro', 6);
                    $tabla.="<tr><td>EL aspirante $nombre Cumple con los requisitos segun su nueva calificaciòn</td></tr>";
                } elseif ($cumple == 0) {
                    $asp->id = $aspirante['Candidate']['id'];
                    $asp->saveField('estado_filtro', 7);
                    $tabla.="<tr><td>EL aspirante $nombre  no cumple con los requisitos segun su nueva calificaciòn</td></tr>";
                }
            }
            $tabla.="</tbody></table>";

            echo $tabla;
        }





        // }
    }

    function ficha_reclamacion($claim_id) {
        $this->layout = "pdf";
        $this->set('id', $claim_id);
        $id = $this->Session->read('proy_id');
        $this->Claim->recursive = -1;
        $this->set('reclamacion', $this->Claim->find('first', array('conditions' => array('Claim.id' => $claim_id))));
        $this->set('codigo', $this->Session->read('cod'));
    }

}

?>
